To Load Load_StgSpend Table:
Source OLE DB Source Spend Table:
Columns Tab:
Derived Column Empty to NULL
Derived Column Text:
GETDATE()
GETDATE()
ISNULL([Main Document ID]) ? "Unknown" : [Main Document ID]
ISNULL([Work Order ID]) ? "Unknown" : [Work Order ID]
0
ROUND([Invoice Amount],2)
REPLACENULL([Line Item Amount (Vendor & by Rate Category)
(ST/Hr)],"0")
0
ISNULL([Bill Rate (Monthly/MO)]) ? 0 : [Bill Rate (Monthly/MO)]
ISNULL([Invoice Line Item Amount]) ? 0 : [Invoice Line Item Amount]
ISNULL([Time Sheet Amount (ST/Hr)]) ? 0 : [Time Sheet Amount (ST/Hr)]
ISNULL([Time Sheet Amount (OT/Hr)]) ? 0 : [Time Sheet Amount (OT/Hr)]
ISNULL([Credit/Debit Memo Amount (DT/Hr)]) ? 0 : [Credit/Debit Memo
Amount (DT/Hr)]
ISNULL([Credit/Debit Memo Amount (OT/Hr)]) ? 0 : [Credit/Debit Memo
Amount (OT/Hr)]
ISNULL([Credit/Debit Memo Amount (DT/Hr)]) ? 0 : [Credit/Debit Memo
Amount (DT/Hr)]
ISNULL([Tax Amount]) ? 0 : [Tax Amount]
ISNULL([Bill Rate (OT/Hr)]) ? 0 : [Bill Rate (OT/Hr)]
ISNULL([Bill Rate (DT/Hr)]) ? 0 : [Bill Rate (DT/Hr)]
ISNULL([MSP Amount]) ? 0 : [MSP Amount]
ISNULL([Credit/Debit Memo Amount (ST/Hr)]) ? 0 : [Credit/Debit Memo
Amount (ST/Hr)]
ISNULL([Line Item Amount (Vendor & by Rate Category) (OT/Hr)]) ?
0 : [Line Item Amount (Vendor & by Rate Category) (OT/Hr)]
ISNULL([Line Item Amount (Vendor & by Rate Category) (DT/Hr)]) ?
0 : [Line Item Amount (Vendor & by Rate Category) (DT/Hr)]
ISNULL([Line Item Amount (Vendor & by Rate Category)
(Monthly/MO)]) ? 0 : [Line Item Amount (Vendor & by Rate Category)
(Monthly/MO)]
ISNULL([Billable Hours (Monthly/MO)]) ? 0 : [Billable Hours
(Monthly/MO)]
ISNULL([Time Sheet Amount (Monthly/MO)]) ? 0 : [Time Sheet Amount
(Monthly/MO)]
ISNULL([Credit/Debit Memo Amount (Monthly/MO)]) ? 0 : [Credit/Debit
Memo Amount (Monthly/MO)]
Data Conversion:
OLE DB Destination:
Mappings Tab:
To Load_StgInvoice Table:
Source Alt_Spend:
Column Tab:
Data Conversions:
Derived Columns:
Derived Columns Text:
GETDATE()
GETDATE()
ISNULL([Job Seeker ID]) ?
"Unkown" : [Job Seeker ID]
ISNULL([Diverse Supplier Filter
1]) ? "Unkown" : [Diverse Supplier Filter 1]
ISNULL([Main Document ID]) ?
"Unknown" : [Main Document ID]
ISNULL([Work Order ID]) ?
"Unknown" : [Work Order ID]
ISNULL([Invoice ID]) ?
"Unknown" : [Invoice ID]
ISNULL([Consolidated Invoice ID])
? "Unknown" : [Consolidated Invoice ID]
ISNULL([Invoice Type]) ?
"Unknown" : [Invoice Type]
ISNULL([Invoice Line Item Type])
? "Unknown" : [Invoice Line Item Type]
ISNULL([Invoice Line Item ID]) ?
"Unknown" : [Invoice Line Item ID]
ISNULL([Worker Type]) ?
"Unknown" : [Worker Type]
ISNULL(Currency) ?
"Unknown" : Currency
ISNULL([Diverse?]) ?
"Unknown" : [Diverse?]
ISNULL([Tier 1 Supplier?]) ?
"Unknown" : [Tier 1 Supplier?]
ISNULL([Copy of Consolidated
Invoice End Date]) ? "Unknown" : [Copy of Consolidated Invoice End
Date]
ISNULL([Copy of Invoice Line Item
Date]) ? "Unknown" : [Copy of Invoice Line Item Date]
ISNULL([Copy of Invoice Line Item
Start Date]) ? "Unknown" : [Copy of Invoice Line Item Start Date]
ISNULL([Copy of Invoice Approved
Date]) ? "Unknown" : [Copy of Invoice Approved Date]
ISNULL([Copy of Time Sheet Submit
Date]) ? "Unknown" : [Copy of Time Sheet Submit Date]
ISNULL([Copy of Expense Sheet
Approved Date]) ? "Unknown" : [Copy of Expense Sheet Approved Date]
ISNULL([Copy of Time Sheet
Approved Date]) ? "Unknown" : [Copy of Time Sheet Approved Date]
Conditional Split:
Derived Column 1:
Union All:
OLE DB Destination
Mappings Tab:
Multicast Transformation
https://www.sqlshack.com/ssis-multicast-transformation-overview/